﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Globalization;
using VietKinsNutrition.Common;
using VietKinsNutrition.Data;

namespace VietKinsNutrition.Bussiness
{
    public class ClsDanhsachNV
    {



        #region VarPublic
        public static string SaveResouceFile(string FileName, Stream stream)
        {
            string FilePath = Path.GetTempPath() + FileName;
            FileStream fs = new FileStream(FilePath, FileMode.Create);
            BinaryReader br = new BinaryReader(stream);
            byte[] save = new byte[stream.Length];
            br.Read(save, 0, save.Length);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(save, 0, save.Length);
            bw.Flush();
            bw.Close();
            return FilePath;
        }
        public static void KillExcelProcess()
        {
            try
            {
                foreach (Process thisproc in Process.GetProcessesByName("excel"))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch (Exception Exc)
            {
                Exc.Source = "";
            }
        }





        #endregion
        #region FunctionPublic_Report
        #region Private

        public static void Danhsachnhanvien(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, System.Data.DataTable Tblcot)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_danhsachnv(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, Tblcot);
            }
            catch
            {
            }
        }
        public static void Xeploainhanvien(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_xeploainv(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
            }
            catch
            {
            }
        }
        public static void ThongtinTo(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ThongtinTo(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
            }
            catch
            {
            }
        }
        public static void XeploaiTo(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_XeploaiTo(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
            }
            catch
            {
            }
        }
        public static void Danhsachnhanvien_truong(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, System.Data.DataTable Tblcot)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_danhsachnv_truong(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, Tblcot);
            }
            catch
            {
            }
        }
        public static void Hoadon_nhap(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, string Tongtien, int SHD, System.Data.DataTable Tblnhanvien, string ngay)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_Hoadonnhap(oSheet, tenso, tenphong, tentruong, tieude, Tongtien, SHD, Tblnhanvien, ngay);
                oXL.Visible = true;
            }
            catch
            {
            }
        }

        public static void Hoadon_nhap(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, string Tongtien, int SHD, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_Hoadonnhap(oSheet, tenso, tenphong, tentruong, tieude, Tongtien, SHD, Tblnhanvien);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void Hoadon_chi(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, string Tongtien, int SHD, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_Hoadonchi(oSheet, tenso, tenphong, tentruong, tieude, Tongtien, SHD, Tblnhanvien);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void Hoadon_xuat(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tong)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_Hoadonxuat(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, tong);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void ThongkeDD(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, string time, System.Data.DataTable Tblnhanvien, System.Windows.Forms.DataGridView tblPLG)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ThongkeDD(oSheet, tenso, tenphong, tentruong, tieude, time, Tblnhanvien, tblPLG);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void ThongkeDD(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, string time, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ThongkeDD(oSheet, tenso, tenphong, tentruong, tieude, time, Tblnhanvien);
                oXL.Visible = true;
            }
            catch
            {
            }
        }

        public static void baocaotuan(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Windows.Forms.DataGridView Tblnhanvien, System.Windows.Forms.DataGridView Tbltong, string[] s)
        {
            try
            {
                KillExcelProcess();
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);

                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_baocaotuan(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, Tbltong, s);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void Tonghopthuchi(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_tonghopthuchi(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
            }
            catch
            {
            }
        }
        public static void Tonghopchi(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, string tongchi, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_tonghopchi(oSheet, tenso, tenphong, tentruong, tieude, tongchi, Tblnhanvien);
            }
            catch
            {
            }
        }



        private static void Execute_danhsachnv(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, System.Data.DataTable Tblcot)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("C5", "C5").Value2 = "Ngày báo cáo: " + time;
                //if (tieude != "")
                //    oSheet.get_Range("E4", "E4").Value2 = tieude;
                //string Start = "A";
                //string End = ExcelFunc.NextChar(Start, TblBaocaothongke.Columns.Count - 1);
                //if (tieude != "")
                //{
                //    Microsoft.Office.Interop.Excel.Range rang;
                //    rang = oSheet.get_Range(Start + "4", End + "4");
                //    rang.Merge(false);
                //    rang.Font.Size = "14";
                //    rang.Value2 = tieude;
                //    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                //    rang = oSheet.get_Range(Start + "5", End + "5");
                //    rang.Merge(false);
                //    rang.Value2 = "Ngày báo cáo: " + time;
                //    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                //}

                string colStart = "A";
                for (int i = 0; i < Tblcot.Rows.Count; i++)
                {
                    if (bool.Parse(Tblcot.Rows[i]["trangthai"].ToString()) == false)
                    {
                        Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(colStart + "6", colStart + (int.Parse(Tblnhanvien.Rows.Count.ToString()) + 6).ToString());
                        range.Delete(XlDeleteShiftDirection.xlShiftToLeft);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                    }
                    else
                        colStart = ExcelFunc.NextChar(colStart);
                }

                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                {
                    AutoFitColumn(m, oSheet);
                }

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_danhsachnv_truong(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, System.Data.DataTable Tblcot)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                //  oSheet.get_Range("F5", "F5").Value2 = "Ngày báo cáo: " + time;
                string Start = "A";
                string End = ExcelFunc.NextChar(Start, TblBaocaothongke.Columns.Count - 1);
                if (tieude != "")
                {
                    Microsoft.Office.Interop.Excel.Range rang;
                    rang = oSheet.get_Range(Start + "4", End + "4");
                    rang.Merge(false);
                    rang.Font.Size = "14";
                    rang.Value2 = tieude;
                    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                    rang = oSheet.get_Range(Start + "5", End + "5");
                    rang.Merge(false);
                    rang.Value2 = "Ngày báo cáo: " + time;
                    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                }

                string colStart = "A";
                for (int i = 0; i < Tblcot.Rows.Count; i++)
                {
                    if (bool.Parse(Tblcot.Rows[i]["trangthai"].ToString()) == false)
                    {
                        Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(colStart + "6", colStart + (int.Parse(Tblnhanvien.Rows.Count.ToString()) + 6).ToString());
                        range.Delete(XlDeleteShiftDirection.xlShiftToLeft);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                    }
                    else
                        colStart = ExcelFunc.NextChar(colStart);
                }

                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                {
                    AutoFitColumn(m, oSheet);
                }

            }
            catch (Exception ex)
            { }
        }

        private static void AutoFitColumn(int column, Microsoft.Office.Interop.Excel._Worksheet sheet)
        {
            ((Range)sheet.Cells[1, column]).EntireColumn.AutoFit();
        }
        private static void Execute_xeploainv(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;

                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ThongtinTo(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                {
                    AutoFitColumn(m, oSheet);
                }

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_XeploaiTo(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("C5", "C5").Value2 = "Ngày báo cáo: " + time;

                int j = 6;
                string a = "C";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "C";
                }

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_Hoadonchi(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, string Tongtien, int SHD, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A7", "A7").Value2 = "Ngày : " + time;
                //oSheet.get_Range("A6", "A6").Value2 = "Số HĐ : " + SHD;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 8;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A9", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    // if (i != (TblBaocaothongke.Rows.Count - 1))
                    oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                Microsoft.Office.Interop.Excel.Range rang = oSheet.get_Range("E" + (9 + TblBaocaothongke.Rows.Count).ToString(), "F" + (9 + TblBaocaothongke.Rows.Count).ToString());
                oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
                rang.Merge(false);
                rang.Value2 = Tongtien;
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                rang = oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "F" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.RowHeight = 30;

                rang = oSheet.get_Range("B" + (11 + TblBaocaothongke.Rows.Count).ToString(), "D" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người nhận hàng";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;

                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).Value2 = "Người giao hàng";
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).Font.Bold = System.Drawing.FontStyle.Bold;
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                rang = oSheet.get_Range("E" + (11 + TblBaocaothongke.Rows.Count).ToString(), "F" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người lập hóa đơn";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;



            }
            catch (Exception ex)
            { }
        }

        private static void Execute_Hoadonnhap(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, string Tongtien, int SHD, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A7", "A7").Value2 = "Ngày : " + time;
                oSheet.get_Range("A6", "A6").Value2 = "Số HĐ : " + SHD;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 8;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A9", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";

                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    // if (i != (TblBaocaothongke.Rows.Count - 1))
                    oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        int s1; string tg = ""; string test = "0"; double d = 0;
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();
                        if (a == "C" || a == "D" || a == "E" || a == "F")
                        {
                            if (a == "E" || a == "F")
                            {
                                d = double.Parse(test);
                                tg = d.ToString("N0", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                            }
                            else
                            {
                                d = double.Parse(test);
                                tg = d.ToString("N2", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;

                            }

                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                Microsoft.Office.Interop.Excel.Range rang = oSheet.get_Range("E" + (9 + TblBaocaothongke.Rows.Count).ToString(), "F" + (9 + TblBaocaothongke.Rows.Count).ToString());
                oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
                rang.Merge(false);
                //  int tt = int.Parse(Tongtien);
                rang.Value2 = "Tổng tiền: " + Tongtien;
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = true;


                rang = oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "F" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.RowHeight = 30;

                rang = oSheet.get_Range("B" + (11 + TblBaocaothongke.Rows.Count).ToString(), "D" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người nhận hàng";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;

                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).Value2 = "Người giao hàng";
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).Font.Bold = System.Drawing.FontStyle.Bold;
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                if (tieude != "HÓA ĐƠN XUẤT HÀNG TRONG NGÀY ")
                    oSheet.get_Range("D8", "D8").Value2 = "SL nhập";

                rang = oSheet.get_Range("E" + (11 + TblBaocaothongke.Rows.Count).ToString(), "F" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người lập hóa đơn";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;



            }
            catch (Exception ex)
            { }
        }


        private static void Execute_Hoadonnhap(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, string Tongtien, int SHD, System.Data.DataTable Tblnhanvien, string ngay)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                //  oSheet.get_Range("A7", "A7").Value2 = "Ngày : " + ngay;
                oSheet.get_Range("B7", "B7").Value2 = ngay;
                oSheet.get_Range("A6", "A6").Value2 = "Số HĐ : " + SHD;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 8;
                string a = "B";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A9", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";

                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    // if (i != (TblBaocaothongke.Rows.Count - 1))
                    oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        int s1; string tg = ""; string test = "0"; double d = 0;
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();
                        if (a == "D" || a == "E" || a == "F" || a == "G")
                        {
                            if (a == "F" || a == "G")
                            {
                                d = double.Parse(test);
                                tg = d.ToString("N0", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                            }
                            else
                            {
                                d = double.Parse(test);
                                tg = d.ToString("N2", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;

                            }

                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];


                        a = ExcelFunc.NextChar(a);

                    }
                    oSheet.get_Range("A" + j.ToString(), "A" + j.ToString()).Value2 = "=ROW()-8";

                    a = "B";
                }
                Microsoft.Office.Interop.Excel.Range rang = oSheet.get_Range("F" + (9 + TblBaocaothongke.Rows.Count).ToString(), "G" + (9 + TblBaocaothongke.Rows.Count).ToString());
                oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
                rang.Merge(false);
                //  int tt = int.Parse(Tongtien);
                rang.Value2 = "Tổng tiền: " + Tongtien;
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = true;


                rang = oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "G" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.RowHeight = 30;

                rang = oSheet.get_Range("C" + (11 + TblBaocaothongke.Rows.Count).ToString(), "E" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người nhận hàng";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;
                rang = oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "B" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "B" + (11 + TblBaocaothongke.Rows.Count).ToString()).Value2 = "Người giao hàng";
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "B" + (11 + TblBaocaothongke.Rows.Count).ToString()).Font.Bold = System.Drawing.FontStyle.Bold;
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "B" + (11 + TblBaocaothongke.Rows.Count).ToString()).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                if (tieude != "HÓA ĐƠN XUẤT HÀNG TRONG NGÀY ")
                    oSheet.get_Range("E8", "E8").Value2 = "SL nhập";

                rang = oSheet.get_Range("F" + (11 + TblBaocaothongke.Rows.Count).ToString(), "G" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người lập hóa đơn";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;



            }
            catch (Exception ex)
            { }
        }


        private static void Execute_Hoadonxuat(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tong)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;


                int j = 6;
                string a = "B";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        double s; string tg = ""; string test = "0";
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();
                        if (a == "E" || a == "F" || a == "D")
                        {
                            if (a != "D")
                            {
                                s = double.Parse(test);
                                tg = s.ToString("N0", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                            }
                            else
                            {
                                s = double.Parse(test);
                                tg = s.ToString("N2", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                            }
                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    oSheet.get_Range("A" + j.ToString(), "A" + j.ToString()).Value2 = "=ROW()-6";

                    a = "B";
                }

                oSheet.get_Range("F" + (7 + TblBaocaothongke.Rows.Count), "F" + (7 + TblBaocaothongke.Rows.Count)).Value2 = tong;



            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ThongkeDD(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, string time, System.Data.DataTable Tblnhanvien, System.Windows.Forms.DataGridView TblPLG)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                //  string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                //for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                //{
                //    AutoFitColumn(m, oSheet);
                //}


                oSheet.get_Range("C" + (8 + Tblnhanvien.Rows.Count), "C" + (8 + Tblnhanvien.Rows.Count)).Value2 = "Nhu cầu dinh dưỡng";
                oSheet.get_Range("C" + (9 + Tblnhanvien.Rows.Count), "C" + (9 + Tblnhanvien.Rows.Count)).Value2 = "Dinh dưỡng thực tế";
                oSheet.get_Range("C" + (10 + Tblnhanvien.Rows.Count), "C" + (10 + Tblnhanvien.Rows.Count)).Value2 = " TLĐ(%)";
                oSheet.get_Range("C" + (11 + Tblnhanvien.Rows.Count), "C" + (11 + Tblnhanvien.Rows.Count)).Value2 = "P:L:G";

                //oSheet.get_Range("C" + (7 + Tblnhanvien.Rows.Count), "C" + (7 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[0].Cells[0].Value;
                //oSheet.get_Range("C" + (8 + Tblnhanvien.Rows.Count), "C" + (8 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[1].Cells[0].Value;
                //oSheet.get_Range("C" + (9 + Tblnhanvien.Rows.Count), "C" + (9 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[2].Cells[0].Value;
                //oSheet.get_Range("C" + (10 + Tblnhanvien.Rows.Count), "C" + (10 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[3].Cells[0].Value;


                oSheet.get_Range("E" + (8 + Tblnhanvien.Rows.Count), "E" + (8 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[0].Cells[3].Value;
                oSheet.get_Range("E" + (9 + Tblnhanvien.Rows.Count), "E" + (9 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[1].Cells[3].Value;
                oSheet.get_Range("E" + (10 + Tblnhanvien.Rows.Count), "E" + (10 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[2].Cells[3].Value;
                oSheet.get_Range("E" + (11 + Tblnhanvien.Rows.Count), "E" + (11 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[3].Cells[3].Value;

                oSheet.get_Range("F" + (8 + Tblnhanvien.Rows.Count), "F" + (8 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[0].Cells[5].Value;
                oSheet.get_Range("F" + (9 + Tblnhanvien.Rows.Count), "F" + (9 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[1].Cells[5].Value;
                oSheet.get_Range("F" + (10 + Tblnhanvien.Rows.Count), "F" + (10 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[2].Cells[5].Value;
                oSheet.get_Range("F" + (11 + Tblnhanvien.Rows.Count), "F" + (11 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[3].Cells[5].Value;

                oSheet.get_Range("G" + (8 + Tblnhanvien.Rows.Count), "G" + (8 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[0].Cells[7].Value;
                oSheet.get_Range("G" + (9 + Tblnhanvien.Rows.Count), "G" + (9 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[1].Cells[7].Value;
                oSheet.get_Range("G" + (10 + Tblnhanvien.Rows.Count), "G" + (10 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[2].Cells[7].Value;
                oSheet.get_Range("G" + (11 + Tblnhanvien.Rows.Count), "G" + (11 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[3].Cells[7].Value;

                oSheet.get_Range("H" + (8 + Tblnhanvien.Rows.Count), "H" + (8 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[0].Cells[9].Value;
                oSheet.get_Range("H" + (9 + Tblnhanvien.Rows.Count), "H" + (9 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[1].Cells[9].Value;
                oSheet.get_Range("H" + (10 + Tblnhanvien.Rows.Count), "H" + (10 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[2].Cells[9].Value;
                oSheet.get_Range("H" + (11 + Tblnhanvien.Rows.Count), "H" + (11 + Tblnhanvien.Rows.Count)).Value2 = TblPLG.Rows[3].Cells[9].Value;




            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ThongkeDD(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, string time, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                //  string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                //for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                //{
                //    AutoFitColumn(m, oSheet);
                //}

            }
            catch (Exception ex)
            { }
        }

        private static void Execute_baocaotuan(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Windows.Forms.DataGridView Tblnhanvien, System.Windows.Forms.DataGridView Tbltong, string[] s)
        {
            string StartChar;


            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;

                Microsoft.Office.Interop.Excel.Range rang;
                string start = "D";

                for (int r = 0; r < 6; r++)
                {
                    oSheet.get_Range(start + 7, start + 7).Value2 = s[r];
                    start = ExcelFunc.NextChar(start);
                    oSheet.get_Range(start + 7, start + 7).ColumnWidth = 5.75;
                }
                int j = 8;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A9", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";
                for (int i = 0; i < Tblnhanvien.RowCount; i++)
                {
                    j = j + 1;
                    if (i != (Tblnhanvien.RowCount - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);


                    for (int b = 0; b < Tblnhanvien.ColumnCount; b++)
                    {
                        if (Tblnhanvien.Rows[i].Cells[b].Value != null)
                        {
                            float s1; string tg = ""; string test = "0";
                            if (a != "A" && a != "B" && a != "C")
                            {
                                if (Tblnhanvien.Rows[i].Cells[b].Value.ToString() != "")
                                    test = Tblnhanvien.Rows[i].Cells[b].Value.ToString();

                                s1 = float.Parse(test);
                                tg = s1.ToString("N2", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                            }
                            else

                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = Tblnhanvien.Rows[i].Cells[b].Value.ToString();
                        }
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";

                }

                if (Tbltong.Rows[0].Cells[1].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[0].Cells[1].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("K" + (9 + Tblnhanvien.RowCount), "K" + (9 + Tblnhanvien.RowCount)).Value2 = tg;

                    //  oSheet.get_Range("K" + (9 + Tblnhanvien.RowCount), "K" + (9 + Tblnhanvien.RowCount)).Value2 = Tbltong.Rows[0].Cells[1].Value;
                }
                if (Tbltong.Rows[1].Cells[1].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[1].Cells[1].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("K" + (10 + Tblnhanvien.RowCount), "K" + (10 + Tblnhanvien.RowCount)).Value2 = tg;
                }
                //if (Tbltong.Rows[2].Cells[1].Value.ToString() != "")
                //{
                //    float s1; string tg = ""; string test = "0";
                //    test = Tbltong.Rows[2].Cells[1].Value.ToString();

                //    s1 = float.Parse(test);
                //    tg = s1.ToString("N2", nfi);
                //            oSheet.get_Range("K" + (11 + Tblnhanvien.RowCount), "K" + (11 + Tblnhanvien.RowCount)).Value2 = Tbltong.Rows[2].Cells[1].Value.ToString();
                oSheet.get_Range("K" + (11 + Tblnhanvien.RowCount), "K" + (11 + Tblnhanvien.RowCount)).Value2 = Tbltong.Rows[2].Cells[1].Value;
                //     }
                if (Tbltong.Rows[0].Cells[2].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[0].Cells[2].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("L" + (9 + Tblnhanvien.RowCount), "L" + (9 + Tblnhanvien.RowCount)).Value2 = tg;
                }
                if (Tbltong.Rows[1].Cells[2].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[1].Cells[2].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("L" + (10 + Tblnhanvien.RowCount), "L" + (10 + Tblnhanvien.RowCount)).Value2 = tg;
                }
                //if (Tbltong.Rows[2].Cells[2].Value.ToString() != "")
                //{
                //    float s1; string tg = ""; string test = "0";
                //    test = Tbltong.Rows[2].Cells[2].Value.ToString();

                //    s1 = float.Parse(test);
                //    tg = s1.ToString("N2", nfi);
                oSheet.get_Range("L" + (11 + Tblnhanvien.RowCount), "L" + (11 + Tblnhanvien.RowCount)).Value2 = Tbltong.Rows[2].Cells[2].Value.ToString();
                //  }
                if (Tbltong.Rows[0].Cells[3].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[0].Cells[3].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("N" + (9 + Tblnhanvien.RowCount), "N" + (9 + Tblnhanvien.RowCount)).Value2 = tg;
                }
                if (Tbltong.Rows[1].Cells[3].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[1].Cells[3].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("N" + (10 + Tblnhanvien.RowCount), "N" + (10 + Tblnhanvien.RowCount)).Value2 = tg;
                }
                //if (Tbltong.Rows[2].Cells[3].Value.ToString() != "")
                //{
                //    float s1; string tg = ""; string test = "0";
                //    test = Tbltong.Rows[2].Cells[3].Value.ToString();

                //    s1 = float.Parse(test);
                //    tg = s1.ToString("N2", nfi);
                oSheet.get_Range("N" + (11 + Tblnhanvien.RowCount), "N" + (11 + Tblnhanvien.RowCount)).Value2 = Tbltong.Rows[2].Cells[3].Value.ToString();
                //  }
                if (Tbltong.Rows[0].Cells[4].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[0].Cells[4].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("P" + (9 + Tblnhanvien.RowCount), "P" + (9 + Tblnhanvien.RowCount)).Value2 = tg;
                }
                if (Tbltong.Rows[1].Cells[4].Value.ToString() != "")
                {
                    float s1; string tg = ""; string test = "0";
                    test = Tbltong.Rows[1].Cells[4].Value.ToString();

                    s1 = float.Parse(test);
                    tg = s1.ToString("N2", nfi);
                    oSheet.get_Range("P" + (10 + Tblnhanvien.RowCount), "P" + (10 + Tblnhanvien.RowCount)).Value2 = tg;
                }
                //if (Tbltong.Rows[2].Cells[4].Value.ToString() != "")
                //{
                //    float s1; string tg = ""; string test = "0";
                //    test = Tbltong.Rows[2].Cells[4].Value.ToString();

                //    s1 = float.Parse(test);
                //    tg = s1.ToString("N2", nfi);
                oSheet.get_Range("P" + (11 + Tblnhanvien.RowCount), "P" + (11 + Tblnhanvien.RowCount)).Value2 = Tbltong.Rows[2].Cells[4].Value.ToString();
                // }

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_tonghopthuchi(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;
            NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
            nfi.NumberGroupSeparator = ".";
            nfi.NumberDecimalSeparator = ",";
            double tt = 0;
            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;

                if (tieude == "TỔNG HỢP ĐI CHỢ")
                {
                    oSheet.get_Range("C6", "C6").Value2 = "SL nhập";
                    oSheet.get_Range("G6", "G6").Value2 = "Ngày nhập";
                }

                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        int s; string tg = ""; string test = "0"; double s1;
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();
                        if (a == "C" || a == "E" || a == "F")
                        {
                            if (a == "C")
                            {
                                s1 = double.Parse(test);
                                tg = s1.ToString("N2", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                            }
                            if (a == "F")
                            {
                                s1 = double.Parse(test);
                                tg = s1.ToString("N0", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                                tt += s1;
                            }
                            if (a == "E")
                            {
                                s = int.Parse(test);
                                tg = s.ToString("N0", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;

                            }


                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                Range rang;
                rang = oSheet.get_Range("E" + (7 + TblBaocaothongke.Rows.Count), "E" + (7 + TblBaocaothongke.Rows.Count));
                rang.Value2 = "Tổng tiền: ";
                //    rang.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                rang.Font.Bold = true;
                rang = oSheet.get_Range("F" + (7 + TblBaocaothongke.Rows.Count), "F" + (7 + TblBaocaothongke.Rows.Count));
                //        rang.Value2 = "=SUM(F7:F" + (6 + TblBaocaothongke.Rows.Count).ToString() + ")";
                rang.Value2 = "'" + tt.ToString("N0", nfi);
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                rang.Font.Bold = true;

                oSheet.PageSetup.CenterHorizontally = true;
            }
            catch (Exception ex)
            { }
        }



        private static void Execute_tonghopchi(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, string Tongchi, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;

                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {

                        oSheet.get_Range(a + (j).ToString(), a + (j).ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                //

                Microsoft.Office.Interop.Excel.Range rang = oSheet.get_Range("D" + (9 + TblBaocaothongke.Rows.Count).ToString(), "E" + (9 + TblBaocaothongke.Rows.Count).ToString());
                // oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
                rang.Merge(false);
                rang.Value2 = Tongchi;
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;

                rang = oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "F" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.RowHeight = 30;

                rang = oSheet.get_Range("B" + (11 + TblBaocaothongke.Rows.Count).ToString(), "D" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người nhận hàng";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;

                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).Value2 = "Người giao hàng";
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).Font.Bold = System.Drawing.FontStyle.Bold;
                oSheet.get_Range("A" + (11 + TblBaocaothongke.Rows.Count).ToString(), "A" + (11 + TblBaocaothongke.Rows.Count).ToString()).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                rang = oSheet.get_Range("E" + (11 + TblBaocaothongke.Rows.Count).ToString(), "F" + (11 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Người lập hóa đơn";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                rang.Font.Bold = System.Drawing.FontStyle.Bold;

            }
            catch (Exception ex)
            { }
        }



        #endregion
        #endregion

        public static void Danhsachsknv(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_sknv(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
            }
            catch
            {
            }
        }
        private static void Execute_sknv(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;
            int sieugay = 0; int gay = 0; int bt = 0; int thuacan = 0; int tienbeo = 0; int beo1 = 0; int beo2 = 0; int beo3 = 0;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                //if (tieude != "")
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                //string Start = "A";
                //string End = ExcelFunc.NextChar(Start, TblBaocaothongke.Columns.Count - 1);
                //if (tieude != "")
                //{
                //    Microsoft.Office.Interop.Excel.Range rang;
                //    rang = oSheet.get_Range(Start + "4", End + "4");
                //    rang.Merge(false);
                //    rang.Font.Size = "14";
                //    rang.Value2 = tieude;
                //    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                //    rang = oSheet.get_Range(Start + "5", End + "5");
                //    rang.Merge(false);
                //    rang.Value2 = "Ngày báo cáo: " + time;
                //    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                //}

                //string colStart = "A";
                //for (int i = 0; i < Tblcot.Rows.Count; i++)
                //{
                //    if (bool.Parse(Tblcot.Rows[i]["trangthai"].ToString()) == false)
                //    {
                //        Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(colStart + "6", colStart + (int.Parse(Tblnhanvien.Rows.Count.ToString()) + 6).ToString());
                //        range.Delete(XlDeleteShiftDirection.xlShiftToLeft);
                //        System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                //    }
                //    else
                //        colStart = ExcelFunc.NextChar(colStart);
                //}
                bool tg = true;
                Range rang;
                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    //for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    //{
                    //    oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                    //    a = ExcelFunc.NextChar(a);

                    //}
                    for (int b = 0; b < 7; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        rang = oSheet.get_Range(a + j.ToString(), "L" + j.ToString());
                        if (tg == false)
                        {
                            rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightYellow);
                        }
                        a = ExcelFunc.NextChar(a);

                    }
                    if (tg == true)
                        tg = false;
                    else
                        tg = true;


                    rang = oSheet.get_Range("g" + j.ToString(), "h" + j.ToString());
                    rang.Merge(false);
                    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                    rang = oSheet.get_Range("i" + j.ToString(), "k" + j.ToString());
                    rang.Merge(false);
                    rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                    oSheet.get_Range("i" + j.ToString(), "i" + j.ToString()).Value2 = TblBaocaothongke.Rows[i][7];
                    oSheet.get_Range("l" + j.ToString(), "l" + j.ToString()).Value2 = TblBaocaothongke.Rows[i][8];
                    a = "A";


                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Người siêu gầy")
                        sieugay += 1;
                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Người gầy")
                        gay += 1;
                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Bình thường")
                        bt += 1;
                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Thừa cân")
                        thuacan += 1;
                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Tiền béo phì")
                        tienbeo += 1;
                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Béo phì độ I")
                        beo1 += 1;
                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Béo phì độ II")
                        beo2 += 1;
                    if (TblBaocaothongke.Rows[i]["danhgia"].ToString() == "Béo phì độ III")
                        beo3 += 1;

                }

                oSheet.get_Range("E" + (10 + TblBaocaothongke.Rows.Count), "E" + (10 + TblBaocaothongke.Rows.Count)).Value2 = sieugay;
                oSheet.get_Range("F" + (10 + TblBaocaothongke.Rows.Count), "F" + (10 + TblBaocaothongke.Rows.Count)).Value2 = gay;
                oSheet.get_Range("G" + (10 + TblBaocaothongke.Rows.Count), "G" + (10 + TblBaocaothongke.Rows.Count)).Value2 = bt;
                oSheet.get_Range("H" + (10 + TblBaocaothongke.Rows.Count), "H" + (10 + TblBaocaothongke.Rows.Count)).Value2 = thuacan;
                oSheet.get_Range("I" + (10 + TblBaocaothongke.Rows.Count), "I" + (10 + TblBaocaothongke.Rows.Count)).Value2 = tienbeo;
                oSheet.get_Range("J" + (10 + TblBaocaothongke.Rows.Count), "J" + (10 + TblBaocaothongke.Rows.Count)).Value2 = beo1;
                oSheet.get_Range("K" + (10 + TblBaocaothongke.Rows.Count), "K" + (10 + TblBaocaothongke.Rows.Count)).Value2 = beo2;
                oSheet.get_Range("L" + (10 + TblBaocaothongke.Rows.Count), "L" + (10 + TblBaocaothongke.Rows.Count)).Value2 = beo3;



                oSheet.PageSetup.CenterHorizontally = true;







            }
            catch (Exception ex)
            { }
        }
        public static void CV_nv(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, int id)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_CV_nv(oSheet, tenso, tenphong, tentruong, tieude, id, 1);
                oXL.Visible = true;
                //   oSheet.PrintPreview(false);
            }
            catch
            {
            }
        }

        private static void Execute_CV_nv(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, int id)
        {

            ClsBaocaotonghopDAL ClsBaocaotonghopdal = new ClsBaocaotonghopDAL();
            System.Data.DataTable TblBaocaothongke1 = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien.Namsinh, TblNhanvien.Gioitinh, TblNhanvien.CMND, TblNhanvien.Noicap, TblNhanvien.Ngaycap, TblNhanvien.Tpbanthan, TblNhanvien.Tpgiadinh, TblNhanvien.Noiohientai, TblDm_Dantoc.Tendantoc, TblDm_Tongiao.Tentongiao, TblDm_Quoctich.Tenquoctich, TblNhanvien.Ngayvaodoan, TblNhanvien.Ngayvaodang, TblNhanvien.Dienthoai, TblNhanvien.Email, TblDm_Loaihopdong.Tenhopdong, TblHopdongnhanvien.Ngayhopdong, TblDm_Hedaotao.Tenhedaotao, TblDm_Chucvu.Tenchucvu, TblNhanviencongtac.Tungay, TblDm_Datchuan.Tendatchuan FROM ((((((((TblHopdongnhanvien INNER JOIN (TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) ON TblHopdongnhanvien.NhanviencongtacId = TblNhanviencongtac.Id) INNER JOIN TblDm_Loaihopdong ON TblHopdongnhanvien.LoaihopdongId = TblDm_Loaihopdong.Id) INNER JOIN TblDm_Dantoc ON TblNhanvien.DantocId = TblDm_Dantoc.Id) INNER JOIN TblDm_Tongiao ON TblNhanvien.TongiaoId = TblDm_Tongiao.Id) INNER JOIN TblDm_Quoctich ON TblNhanvien.QuoctichId = TblDm_Quoctich.Id) INNER JOIN TblDm_Namhoc ON TblNhanviencongtac.NamhocId = TblDm_Namhoc.Id) INNER JOIN TblDm_Hedaotao ON TblNhanvien.HedaotaoId = TblDm_Hedaotao.Id) INNER JOIN TblDm_Chucvu ON TblNhanviencongtac.ChucvuId = TblDm_Chucvu.Id) INNER JOIN TblDm_Datchuan ON TblNhanvien.DatchuanId = TblDm_Datchuan.Id WHERE (((TblDm_Namhoc.Trangthai)=-1) AND ((TblNhanvien.Id)=" + id + "))");
            System.Data.DataTable TblBaocaothongke = TblBaocaothongke1;
            System.Data.DataTable Tbltruong = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT tentruong from tbltruong ");
            System.Data.DataTable Tblkhenthuong = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien_Khenthuongkyluat.Hinhthuc, TblNhanvien_Khenthuongkyluat.Ngay FROM ((TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) INNER JOIN TblDm_Namhoc ON TblNhanviencongtac.NamhocId = TblDm_Namhoc.Id) INNER JOIN TblNhanvien_Khenthuongkyluat ON TblNhanviencongtac.Id = TblNhanvien_Khenthuongkyluat.NhanviencongtacId WHERE (((TblNhanvien_Khenthuongkyluat.Loaihinhthuc)='Khen thưởng') AND ((TblNhanvien.Id)=" + id + ") AND ((TblNhanviencongtac.NamhocId) In (select id from tbldm_namhoc where trangthai =-1)))");
            System.Data.DataTable Tblkyluat = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien_Khenthuongkyluat.Hinhthuc, TblNhanvien_Khenthuongkyluat.Ngay FROM ((TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) INNER JOIN TblDm_Namhoc ON TblNhanviencongtac.NamhocId = TblDm_Namhoc.Id) INNER JOIN TblNhanvien_Khenthuongkyluat ON TblNhanviencongtac.Id = TblNhanvien_Khenthuongkyluat.NhanviencongtacId WHERE (((TblNhanvien_Khenthuongkyluat.Loaihinhthuc)='Kỷ luật') AND ((TblNhanvien.Id)=" + id + ") AND ((TblNhanviencongtac.NamhocId) In (select id from tbldm_namhoc where trangthai=-1)))");
            System.Data.DataTable Tblgiadinh = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien_giadinh.Hoten, TblNhanvien_giadinh.Namsinh, TblNhanvien_giadinh.Nghenghiep, TblDm_Quanhe.Tenquanhe, TblNhanvien.Id FROM TblDm_Quanhe INNER JOIN (TblNhanvien_giadinh INNER JOIN (TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) ON TblNhanvien_giadinh.NhanvienId = TblNhanvien.Id) ON TblDm_Quanhe.Id = TblNhanvien_giadinh.QuanheId WHERE (((TblNhanvien.Id)=" + id + ") AND ((TblNhanviencongtac.NamhocId) In (select id from tbldm_namhoc where trangthai = -1))) ");
            System.Data.DataTable Tblsuckhoe = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TOP 1 TblNhanvien.Ten, TblKhamsuckhoeNV.Chieucao, TblKhamsuckhoeNV.Cannang, TblKhamsuckhoeNV.Danhgia, TblDm_Thanghoc.Thang, TblDm_Thanghoc.Nam FROM ((TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) INNER JOIN TblKhamsuckhoeNV ON TblNhanviencongtac.Id = TblKhamsuckhoeNV.NhanviencongtacId) INNER JOIN TblDm_Thanghoc ON TblKhamsuckhoeNV.ThanghocId = TblDm_Thanghoc.Id WHERE (((TblNhanvien.Id)=" + id + ")) ORDER BY TblDm_Thanghoc.Nam DESC , TblDm_Thanghoc.Thang DESC");
            System.Data.DataTable Tblbangcap = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT DISTINCT TblNhanvien.Id, TblNhanvien.Hotendem, TblNhanvien.Ten, TblBangcap.Tentruong, TblBangcap.Loaitotnghiep, TblBangcap.Chuyennganhdaotao, TblDm_Hedaotao.Tenhedaotao, TblBangcap.Namtotnghiep, TblDm_Loaihinhdaotao.Tenloaihinhdaotao FROM TblDm_Hedaotao INNER JOIN ((TblBangcap INNER JOIN TblNhanvien ON TblBangcap.NhanvienId = TblNhanvien.Id) INNER JOIN TblDm_Loaihinhdaotao ON TblBangcap.LoaihinhdaotaoId = TblDm_Loaihinhdaotao.Id) ON TblDm_Hedaotao.Id = TblBangcap.HedaotaoId WHERE (((TblNhanvien.Id)=" + id + "))");



            try
            {
                DateTime time; string s = ""; string s1 = ""; string s2 = ""; string s3 = ""; string s4 = ""; string s5 = "";
                if (TblBaocaothongke.Rows[0]["ngaycap"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["ngaycap"].ToString());
                    s = time.ToString("dd/MM/yyyy");
                }
                if (TblBaocaothongke.Rows[0]["namsinh"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["namsinh"].ToString());
                    s1 = time.ToString("dd/MM/yyyy");
                }
                if (TblBaocaothongke.Rows[0]["tungay"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["tungay"].ToString());
                    s2 = time.ToString("dd/MM/yyyy");
                }
                if (TblBaocaothongke.Rows[0]["ngayvaodang"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["ngayvaodang"].ToString());
                    s3 = time.ToString("dd/MM/yyyy");
                }
                if (TblBaocaothongke.Rows[0]["ngayhopdong"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["ngayhopdong"].ToString());
                    s4 = time.ToString("dd/MM/yyyy");
                }
                if (TblBaocaothongke.Rows[0]["ngayvaodoan"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["ngayvaodoan"].ToString());
                    s5 = time.ToString("dd/MM/yyyy");
                }

                oSheet.get_Range("C3", "C3").Value2 = "Họ và tên khai sinh: " + TblBaocaothongke.Rows[0]["Hotendem"].ToString().ToUpper() + " " + TblBaocaothongke.Rows[0]["ten"].ToString().ToUpper();

                oSheet.get_Range("D4", "D4").Value2 = s1 + "...........................";

                oSheet.get_Range("E4", "E4").Value2 = "Nam/Nữ: " + TblBaocaothongke.Rows[0]["gioitinh"] + "........";
                oSheet.get_Range("D5", "D5").Value2 = TblBaocaothongke.Rows[0]["noiohientai"] + "....................";
                oSheet.get_Range("D6", "D6").Value2 = "Nhà riêng: " + TblBaocaothongke.Rows[0]["dienthoai"] + ".................";
                oSheet.get_Range("D7", "D7").Value2 = TblBaocaothongke.Rows[0]["email"] + "................";
                oSheet.get_Range("D8", "D8").Value2 = TblBaocaothongke.Rows[0]["tendantoc"] + ".....................";
                oSheet.get_Range("E8", "E8").Value2 = "Tôn giáo: " + TblBaocaothongke.Rows[0]["tentongiao"] + ".........";
                oSheet.get_Range("D9", "D9").Value2 = TblBaocaothongke.Rows[0]["tenquoctich"] + ".................";
                oSheet.get_Range("C11", "C11").Value2 = TblBaocaothongke.Rows[0]["CMND"] + "......................";
                oSheet.get_Range("D11", "D11").Value2 = "Ngày cấp: " + s + "...............";
                oSheet.get_Range("E11", "E11").Value2 = "Nơi cấp: " + TblBaocaothongke.Rows[0]["Noicap"] + ".........";
                oSheet.get_Range("D12", "d12").Value2 = TblBaocaothongke.Rows[0]["tpgiadinh"] + ".......................";
                oSheet.get_Range("D13", "d13").Value2 = TblBaocaothongke.Rows[0]["tpbanthan"] + ".......................";
                oSheet.get_Range("D14", "d14").Value2 = TblBaocaothongke.Rows[0]["tenhedaotao"] + "....................";
                oSheet.get_Range("D15", "d15").Value2 = TblBaocaothongke.Rows[0]["tendatchuan"] + "....................";
                oSheet.get_Range("D16", "d16").Value2 = TblBaocaothongke.Rows[0]["tenchucvu"] + ".....................";
                oSheet.get_Range("d17", "d17").Value2 = "Loại hợp đồng: " + TblBaocaothongke.Rows[0]["tenhopdong"] + ".....................";
                oSheet.get_Range("E17", "E17").Value2 = "Ngày ký: " + s4 + ".......";
                oSheet.get_Range("D20", "d20").Value2 = s3 + ".......................";
                oSheet.get_Range("D21", "d21").Value2 = s5 + ".......................";
                oSheet.get_Range("D22", "d22").Value2 = s2 + ".......................";
                oSheet.get_Range("D19", "d19").Value2 = Tbltruong.Rows[0]["tentruong"] + ".........";
                if (Tblsuckhoe.Rows.Count > 0)
                    oSheet.get_Range("D23", "d23").Value2 = Tblsuckhoe.Rows[0]["danhgia"] + ".........";

                #region khen thưởng, kỷ luật
                string khenthuong = "";
                string kyluat = "";

                if (Tblkhenthuong.Rows.Count > 0)
                {
                    for (int i = 0; i < Tblkhenthuong.Rows.Count; i++)
                    {
                        if (i != 0)
                            khenthuong = khenthuong + ", " + Tblkhenthuong.Rows[i]["hinhthuc"].ToString();
                        else
                            khenthuong = Tblkhenthuong.Rows[i]["hinhthuc"].ToString();
                    }
                }
                if (Tblkyluat.Rows.Count > 0)
                {
                    for (int i = 0; i < Tblkyluat.Rows.Count; i++)
                    {
                        if (i != 0)
                            kyluat = kyluat + ", " + Tblkyluat.Rows[i]["hinhthuc"].ToString();
                        else
                            kyluat = Tblkyluat.Rows[i]["hinhthuc"].ToString();
                    }
                }

                oSheet.get_Range("D24", "d24").Value2 = khenthuong + ".........";
                oSheet.get_Range("D25", "d25").Value2 = kyluat + ".........";

                #endregion

                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A28", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;




                #region  bằng cấp
                if (Tblbangcap.Rows.Count > 0)
                {
                    int j = 0;
                    for (int a = 0; a < Tblbangcap.Rows.Count; a++)
                    {
                        oSheet.get_Range("A" + (27 + j), "a" + (27 + j)).Value2 = " Tên trường: " + Tblbangcap.Rows[a]["tentruong"] + "........";
                        oSheet.get_Range("D" + (27 + j), "D" + (27 + j)).Value2 = " Loại tốt nghiệp: " + Tblbangcap.Rows[a]["loaitotnghiep"] + ".........";
                        oSheet.get_Range("E" + (27 + j), "E" + (27 + j)).Value2 = " Chuyên ngành: " + Tblbangcap.Rows[a]["chuyennganhdaotao"] + ".........";
                        oSheet.get_Range("A" + (28 + j), "A" + (28 + j)).Value2 = " Hệ đào tạo: " + Tblbangcap.Rows[a]["tenhedaotao"] + ".........";
                        oSheet.get_Range("D" + (28 + j), "D" + (28 + j)).Value2 = " Năm tốt nghiệp: " + Tblbangcap.Rows[a]["namtotnghiep"] + ".........";
                        oSheet.get_Range("E" + (28 + j), "E" + (28 + j)).Value2 = " Loại hình đào tạo: " + Tblbangcap.Rows[a]["tenloaihinhdaotao"] + ".........";

                        j = j + 3;

                    }
                }

                #endregion
                Range rang;
                rang = oSheet.get_Range("A" + (27 + (3 * Tblbangcap.Rows.Count)), "A" + (27 + (3 * Tblbangcap.Rows.Count)));
                rang.Value2 = "         THÔNG TIN GIA ĐÌNH";
                rang.Font.Bold = true;

                #region  gia đình

                if (Tblgiadinh.Rows.Count > 0)
                {
                    int j = 0;
                    for (int a = 0; a < Tblgiadinh.Rows.Count; a++)
                    {
                        oSheet.get_Range("A" + (28 + j + (3 * Tblbangcap.Rows.Count)), "a" + (28 + j + (3 * Tblbangcap.Rows.Count))).Value2 = " Họ và tên: " + Tblgiadinh.Rows[a]["hoten"] + "........";
                        //oSheet.get_Range("B" + (28 + (3 * Tblbangcap.Rows.Count)), "B" + (28 + (3 * Tblbangcap.Rows.Count))).Value2 = Tblgiadinh.Rows[a]["hoten"] + "........";
                        oSheet.get_Range("D" + (28 + j + (3 * Tblbangcap.Rows.Count)), "D" + (28 + j + (3 * Tblbangcap.Rows.Count))).Value2 = " Quan hệ: " + Tblgiadinh.Rows[a]["tenquanhe"] + ".........";
                        oSheet.get_Range("E" + (28 + j + (3 * Tblbangcap.Rows.Count)), "E" + (28 + j + (3 * Tblbangcap.Rows.Count))).Value2 = " Năm sinh: " + Tblgiadinh.Rows[a]["namsinh"] + ".........";
                        oSheet.get_Range("B" + (29 + j + (3 * Tblbangcap.Rows.Count)), "B" + (29 + j + (3 * Tblbangcap.Rows.Count))).Value2 = "+ Nghề nghiệp, chức vụ: " + Tblgiadinh.Rows[a]["nghenghiep"] + ".........";
                        j = j + 2;

                    }
                }

                #endregion


            }
            catch (Exception ex)
            { }
        }
        private static void Execute_CV_nv(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, int id, int ibtn)
        {

            ClsBaocaotonghopDAL ClsBaocaotonghopdal = new ClsBaocaotonghopDAL();
            System.Data.DataTable TblBaocaothongke1 = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien.Namsinh, TblNhanvien.Gioitinh, TblNhanvien.CMND, TblNhanvien.Noicap, TblNhanvien.Ngaycap, TblNhanvien.Tpbanthan, TblNhanvien.Tpgiadinh, TblNhanvien.Noiohientai, TblDm_Dantoc.Tendantoc, TblDm_Tongiao.Tentongiao, TblDm_Quoctich.Tenquoctich, TblNhanvien.Ngayvaodoan, TblNhanvien.Ngayvaodang, TblNhanvien.Dienthoai, TblNhanvien.Email, TblDm_Loaihopdong.Tenhopdong, TblHopdongnhanvien.Ngayhopdong, TblDm_Hedaotao.Tenhedaotao, TblDm_Chucvu.Tenchucvu, TblNhanviencongtac.Tungay, TblDm_Datchuan.Tendatchuan FROM ((((((((TblHopdongnhanvien INNER JOIN (TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) ON TblHopdongnhanvien.NhanviencongtacId = TblNhanviencongtac.Id) INNER JOIN TblDm_Loaihopdong ON TblHopdongnhanvien.LoaihopdongId = TblDm_Loaihopdong.Id) INNER JOIN TblDm_Dantoc ON TblNhanvien.DantocId = TblDm_Dantoc.Id) INNER JOIN TblDm_Tongiao ON TblNhanvien.TongiaoId = TblDm_Tongiao.Id) INNER JOIN TblDm_Quoctich ON TblNhanvien.QuoctichId = TblDm_Quoctich.Id) INNER JOIN TblDm_Namhoc ON TblNhanviencongtac.NamhocId = TblDm_Namhoc.Id) INNER JOIN TblDm_Hedaotao ON TblNhanvien.HedaotaoId = TblDm_Hedaotao.Id) INNER JOIN TblDm_Chucvu ON TblNhanviencongtac.ChucvuId = TblDm_Chucvu.Id) INNER JOIN TblDm_Datchuan ON TblNhanvien.DatchuanId = TblDm_Datchuan.Id WHERE (((TblDm_Namhoc.Trangthai)=-1) AND ((TblNhanvien.Id)=" + id + "))");
            System.Data.DataTable TblBaocaothongke = TblBaocaothongke1;
            System.Data.DataTable Tbltruong = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT tentruong from tbltruong ");
            System.Data.DataTable Tblkhenthuong = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien_Khenthuongkyluat.Hinhthuc, TblNhanvien_Khenthuongkyluat.Ngay FROM ((TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) INNER JOIN TblDm_Namhoc ON TblNhanviencongtac.NamhocId = TblDm_Namhoc.Id) INNER JOIN TblNhanvien_Khenthuongkyluat ON TblNhanviencongtac.Id = TblNhanvien_Khenthuongkyluat.NhanviencongtacId WHERE (((TblNhanvien_Khenthuongkyluat.Loaihinhthuc)='Khen thưởng') AND ((TblNhanvien.Id)=" + id + ") AND ((TblNhanviencongtac.NamhocId) In (select id from tbldm_namhoc where trangthai =-1)))");
            System.Data.DataTable Tblkyluat = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien_Khenthuongkyluat.Hinhthuc, TblNhanvien_Khenthuongkyluat.Ngay FROM ((TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) INNER JOIN TblDm_Namhoc ON TblNhanviencongtac.NamhocId = TblDm_Namhoc.Id) INNER JOIN TblNhanvien_Khenthuongkyluat ON TblNhanviencongtac.Id = TblNhanvien_Khenthuongkyluat.NhanviencongtacId WHERE (((TblNhanvien_Khenthuongkyluat.Loaihinhthuc)='Kỷ luật') AND ((TblNhanvien.Id)=" + id + ") AND ((TblNhanviencongtac.NamhocId) In (select id from tbldm_namhoc where trangthai=-1)))");
            System.Data.DataTable Tblgiadinh = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblNhanvien.Hotendem, TblNhanvien.Ten, TblNhanvien_giadinh.Hoten, TblNhanvien_giadinh.Namsinh, TblNhanvien_giadinh.Nghenghiep, TblDm_Quanhe.Tenquanhe, TblNhanvien.Id FROM TblDm_Quanhe INNER JOIN (TblNhanvien_giadinh INNER JOIN (TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) ON TblNhanvien_giadinh.NhanvienId = TblNhanvien.Id) ON TblDm_Quanhe.Id = TblNhanvien_giadinh.QuanheId WHERE (((TblNhanvien.Id)=" + id + ") AND ((TblNhanviencongtac.NamhocId) In (select id from tbldm_namhoc where trangthai = -1))) ");
            System.Data.DataTable Tblsuckhoe = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TOP 1 TblNhanvien.Ten, TblKhamsuckhoeNV.Chieucao, TblKhamsuckhoeNV.Cannang, TblKhamsuckhoeNV.Danhgia, TblDm_Thanghoc.Thang, TblDm_Thanghoc.Nam FROM ((TblNhanvien INNER JOIN TblNhanviencongtac ON TblNhanvien.Id = TblNhanviencongtac.NhanvienId) INNER JOIN TblKhamsuckhoeNV ON TblNhanviencongtac.Id = TblKhamsuckhoeNV.NhanviencongtacId) INNER JOIN TblDm_Thanghoc ON TblKhamsuckhoeNV.ThanghocId = TblDm_Thanghoc.Id WHERE (((TblNhanvien.Id)=" + id + ")and ((TblNhanviencongtac.NamhocId) In (select id from tbldm_namhoc where trangthai =-1)))   ORDER BY TblDm_Thanghoc.Nam DESC , TblDm_Thanghoc.Thang DESC");
            System.Data.DataTable Tblbangcap = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT DISTINCT TblNhanvien.Id, TblNhanvien.Hotendem, TblNhanvien.Ten, TblBangcap.Tentruong, TblBangcap.Loaitotnghiep, TblBangcap.Chuyennganhdaotao, TblDm_Hedaotao.Tenhedaotao, TblBangcap.Namtotnghiep, TblDm_Loaihinhdaotao.Tenloaihinhdaotao FROM TblDm_Hedaotao INNER JOIN ((TblBangcap INNER JOIN TblNhanvien ON TblBangcap.NhanvienId = TblNhanvien.Id) INNER JOIN TblDm_Loaihinhdaotao ON TblBangcap.LoaihinhdaotaoId = TblDm_Loaihinhdaotao.Id) ON TblDm_Hedaotao.Id = TblBangcap.HedaotaoId WHERE (((TblNhanvien.Id)=" + id + "))");



            try
            {


                oSheet.get_Range("D3", "D3").Value2 = TblBaocaothongke.Rows[0]["Hotendem"].ToString().ToUpper() + " " + TblBaocaothongke.Rows[0]["ten"].ToString().ToUpper();

                oSheet.get_Range("D4", "D4").Value2 = TblBaocaothongke.Rows[0]["namsinh"].ToString();

                oSheet.get_Range("G4", "G4").Value2 = TblBaocaothongke.Rows[0]["gioitinh"];
                oSheet.get_Range("D5", "D5").Value2 = TblBaocaothongke.Rows[0]["noiohientai"];
                oSheet.get_Range("D6", "D6").Value2 = "'" + TblBaocaothongke.Rows[0]["dienthoai"];
                oSheet.get_Range("D7", "D7").Value2 = TblBaocaothongke.Rows[0]["email"];
                oSheet.get_Range("D8", "D8").Value2 = TblBaocaothongke.Rows[0]["tendantoc"];
                oSheet.get_Range("G8", "G8").Value2 = TblBaocaothongke.Rows[0]["tentongiao"];
                oSheet.get_Range("D9", "D9").Value2 = TblBaocaothongke.Rows[0]["tenquoctich"];
                oSheet.get_Range("C11", "C11").Value2 = "'" + TblBaocaothongke.Rows[0]["CMND"];
                oSheet.get_Range("E11", "E11").Value2 = TblBaocaothongke.Rows[0]["ngaycap"].ToString();
                oSheet.get_Range("G11", "G11").Value2 = TblBaocaothongke.Rows[0]["Noicap"];
                oSheet.get_Range("D12", "d12").Value2 = TblBaocaothongke.Rows[0]["tpgiadinh"];
                oSheet.get_Range("D13", "d13").Value2 = TblBaocaothongke.Rows[0]["tpbanthan"];
                oSheet.get_Range("D14", "d14").Value2 = TblBaocaothongke.Rows[0]["tenhedaotao"];
                oSheet.get_Range("D15", "d15").Value2 = TblBaocaothongke.Rows[0]["tendatchuan"];
                oSheet.get_Range("D16", "d16").Value2 = TblBaocaothongke.Rows[0]["tenchucvu"];
                oSheet.get_Range("E17", "E17").Value2 = TblBaocaothongke.Rows[0]["tenhopdong"];
                oSheet.get_Range("G17", "G17").Value2 = TblBaocaothongke.Rows[0]["ngayhopdong"].ToString();
                oSheet.get_Range("D20", "d20").Value2 = TblBaocaothongke.Rows[0]["ngayvaodang"].ToString();
                oSheet.get_Range("D21", "d21").Value2 = TblBaocaothongke.Rows[0]["ngayvaodoan"].ToString();
                oSheet.get_Range("D22", "d22").Value2 = TblBaocaothongke.Rows[0]["tungay"].ToString();
                oSheet.get_Range("D19", "d19").Value2 = Tbltruong.Rows[0]["tentruong"];
                if (Tblsuckhoe.Rows.Count > 0)
                    oSheet.get_Range("D23", "d23").Value2 = Tblsuckhoe.Rows[0]["danhgia"];

                #region khen thưởng, kỷ luật
                string khenthuong = "";
                string kyluat = "";

                if (Tblkhenthuong.Rows.Count > 0)
                {
                    for (int i = 0; i < Tblkhenthuong.Rows.Count; i++)
                    {
                        if (i != 0)
                            khenthuong = khenthuong + ", " + Tblkhenthuong.Rows[i]["hinhthuc"].ToString();
                        else
                            khenthuong = Tblkhenthuong.Rows[i]["hinhthuc"].ToString();
                    }
                }
                if (Tblkyluat.Rows.Count > 0)
                {
                    for (int i = 0; i < Tblkyluat.Rows.Count; i++)
                    {
                        if (i != 0)
                            kyluat = kyluat + ", " + Tblkyluat.Rows[i]["hinhthuc"].ToString();
                        else
                            kyluat = Tblkyluat.Rows[i]["hinhthuc"].ToString();
                    }
                }

                oSheet.get_Range("D24", "d24").Value2 = khenthuong;
                oSheet.get_Range("D25", "d25").Value2 = kyluat;

                #endregion

                //const int xlShiftDown = -4121;
                //Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A28", Missing.Value);
                //Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;




                #region  bằng cấp
                if (Tblbangcap.Rows.Count > 0)
                {
                    int j = 0;
                    for (int a = 0; a < Tblbangcap.Rows.Count; a++)
                    {
                        oSheet.get_Range("A" + (27 + j), "a" + (27 + j)).Value2 = " Tên trường: " + Tblbangcap.Rows[a]["tentruong"];
                        oSheet.get_Range("D" + (27 + j), "D" + (27 + j)).Value2 = " Loại tốt nghiệp: " + Tblbangcap.Rows[a]["loaitotnghiep"];
                        oSheet.get_Range("F" + (27 + j), "F" + (27 + j)).Value2 = " Chuyên ngành: " + Tblbangcap.Rows[a]["chuyennganhdaotao"];
                        oSheet.get_Range("A" + (28 + j), "A" + (28 + j)).Value2 = " Hệ đào tạo: " + Tblbangcap.Rows[a]["tenhedaotao"];
                        oSheet.get_Range("D" + (28 + j), "D" + (28 + j)).Value2 = " Năm tốt nghiệp: " + Tblbangcap.Rows[a]["namtotnghiep"];
                        oSheet.get_Range("F" + (28 + j), "F" + (28 + j)).Value2 = " Loại hình ĐT: " + Tblbangcap.Rows[a]["tenloaihinhdaotao"];

                        j = j + 3;

                    }
                }

                #endregion
                Range rang;
                rang = oSheet.get_Range("A" + (27 + (3 * Tblbangcap.Rows.Count)), "A" + (27 + (3 * Tblbangcap.Rows.Count)));
                rang.Value2 = "         THÔNG TIN GIA ĐÌNH";
                rang.Font.Bold = true;

                #region  gia đình

                if (Tblgiadinh.Rows.Count > 0)
                {
                    int j = 0;
                    for (int a = 0; a < Tblgiadinh.Rows.Count; a++)
                    {
                        oSheet.get_Range("A" + (28 + j + (3 * Tblbangcap.Rows.Count)), "a" + (28 + j + (3 * Tblbangcap.Rows.Count))).Value2 = " Họ và tên: " + Tblgiadinh.Rows[a]["hoten"];
                        //oSheet.get_Range("B" + (28 + (3 * Tblbangcap.Rows.Count)), "B" + (28 + (3 * Tblbangcap.Rows.Count))).Value2 = Tblgiadinh.Rows[a]["hoten"] + "........";
                        rang = oSheet.get_Range("D" + (28 + j + (3 * Tblbangcap.Rows.Count)), "E" + (28 + j + (3 * Tblbangcap.Rows.Count)));
                        rang.Merge(false);
                        rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        rang.Value2 = "Quan hệ: " + Tblgiadinh.Rows[a]["tenquanhe"];
                        oSheet.get_Range("F" + (28 + j + (3 * Tblbangcap.Rows.Count)), "F" + (28 + j + (3 * Tblbangcap.Rows.Count))).Value2 = " Năm sinh: " + Tblgiadinh.Rows[a]["namsinh"];
                        oSheet.get_Range("B" + (29 + j + (3 * Tblbangcap.Rows.Count)), "B" + (29 + j + (3 * Tblbangcap.Rows.Count))).Value2 = "+ Nghề nghiệp, chức vụ: " + Tblgiadinh.Rows[a]["nghenghiep"];
                        j = j + 2;

                    }
                }

                #endregion


            }
            catch (Exception ex)
            { }
        }

        public static void Phanbo(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tong)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_phanbo(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, tong);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        private static void Execute_phanbo(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tong)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                int tong_pb = 0;
                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);


                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    tong_pb += int.Parse(TblBaocaothongke.Rows[i][4].ToString());
                    a = "A";
                }
                oSheet.get_Range("E" + (7 + TblBaocaothongke.Rows.Count), "E" + (7 + TblBaocaothongke.Rows.Count)).Value2 = tong;
                oSheet.get_Range("E" + (8 + TblBaocaothongke.Rows.Count), "E" + (8 + TblBaocaothongke.Rows.Count)).Value2 = tong_pb;
                oSheet.get_Range("E" + (9 + TblBaocaothongke.Rows.Count), "E" + (9 + TblBaocaothongke.Rows.Count)).Value2 = int.Parse(tong) - tong_pb;


                //for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                //{
                //    AutoFitColumn(m, oSheet);
                //}

            }
            catch (Exception ex)
            { }
        }


        public static void taisantruong(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tong)
        {
            try
            {
                KillExcelProcess();
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);

                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_taisantruong(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, tong);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        private static void Execute_taisantruong(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tong)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                int tong_pb = 0;
                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);


                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        int s; string tg = ""; string test = "0";
                        if (a == "H" || a == "I")
                        {
                            if (TblBaocaothongke.Rows[i][b].ToString() != "")
                                test = TblBaocaothongke.Rows[i][b].ToString();
                            s = int.Parse(test);
                            tg = s.ToString("N0", nfi);
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                        }
                        else

                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];

                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                //oSheet.get_Range("E" + (7 + TblBaocaothongke.Rows.Count), "E" + (7 + TblBaocaothongke.Rows.Count)).Value2 = tong;
                //oSheet.get_Range("E" + (8 + TblBaocaothongke.Rows.Count), "E" + (8 + TblBaocaothongke.Rows.Count)).Value2 = tong_pb;
                //oSheet.get_Range("E" + (9 + TblBaocaothongke.Rows.Count), "E" + (9 + TblBaocaothongke.Rows.Count)).Value2 = int.Parse(tong) - tong_pb;



            }
            catch (Exception ex)
            { }
        }


        public static void taisan_nv(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                KillExcelProcess();
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);

                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_taisan_nv(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        private static void Execute_taisan_nv(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                int tong_pb = 0; int tong = 0;
                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;
                oSheet.get_Range("A4", "A4").Value2 = tieude;
                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);


                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        int s; string tg = ""; string test = "0"; double s1;
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();
                        if (a == "F" || a == "G" || a == "H")
                        {
                            if (a == "F")
                            {
                                s1 = double.Parse(test);
                                tg = s1.ToString("N2", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                            }
                            if (a == "G")
                            {
                                s = int.Parse(test);
                                tg = s.ToString("N0", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;

                            }
                            if (a == "H")
                            {
                                s = int.Parse(test);
                                tg = s.ToString("N0", nfi);
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;
                                tong += s;
                            }

                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                //           oSheet.get_Range("H" + (7+TblBaocaothongke.Rows.Count), "H"+(7+TblBaocaothongke.Rows.Count)).Value2 = "=SUM(H7:H"+(TblBaocaothongke.Rows.Count+6)+")";
                oSheet.get_Range("H" + (7 + TblBaocaothongke.Rows.Count), "H" + (7 + TblBaocaothongke.Rows.Count)).Value2 = tong.ToString("N0", nfi);



            }
            catch (Exception ex)
            { }
        }




    }
}
